package maindatart.util;

import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;
import maindatart.metadata.MetaDataDao;
import maindatart.metadata.MainDataMetaDataEntity;
import maindatart.metadata.MainDataPropertyEntity;


/**
 * 
 * @author 丁丁
 *
 *	根据主数据的元数据描述，生成主数据存储表的对应SQL语句
 */
public class MainDataSQLBuilder {

	/**
	 * 根据主数据描述，生成主数据存储表SQL
	 * @param mde
	 * @return
	 */
	public static String makeTableSQL(MainDataMetaDataEntity mde){
		String ctable = "create table ";
		
		String tablename = mde.getEnName();
		
		List<MainDataPropertyEntity> pset = mde.getProperties();
		
		String clums = "";
		
		Iterator<MainDataPropertyEntity> ite = pset.iterator();
		
		while(ite.hasNext()){
			MainDataPropertyEntity p = ite.next();
			clums = clums + getClumSQL(p) + " , ";
		}
		if(clums.length() > 3)
		clums = clums.substring(0,clums.length() - 2);
		
		return ctable + tablename + "( " + clums + " )";
	}
	
	
	/**
	 * 获取主数据副本数据存储表的SQL语句
	 * @param mde
	 * @param mis
	 * @return
	 */
	public static String makeCorbonTableSQL(MainDataMetaDataEntity mde,String corbonid){
		
		String ctable = "create table ";
		
		String tablename = mde.getEnName()+corbonid;
		
		List<MainDataPropertyEntity> pset = mde.getProperties();
		
		String clums = "";
		
		Iterator<MainDataPropertyEntity> ite = pset.iterator();
		
		while(ite.hasNext()){
			MainDataPropertyEntity p = ite.next();
			clums = clums + getClumSQL(p) + " , ";
		}
		if(clums.length() > 3)
		clums = clums.substring(0,clums.length() - 2);
		
		return ctable + tablename + "( " + clums + " )";
		
	}
	/**
	 * 获取主数据数量
	 * @param mdd
	 * @return
	 */
	public static String getCountSQL(MainDataMetaDataEntity mdd){
		String tablename = getTableName(mdd);
		
		String sql = "select count (*) from " + tablename;
		
		return sql;
	}
	
	/**
	 * 获取全部主数据
	 * @param mdd
	 * @return
	 */
	public static String getSelectSQL(MainDataMetaDataEntity mdd){
		String ret = "select  ";
		
		List<MainDataPropertyEntity> pset = mdd.getProperties();
		
		String clums = "";
		
		Iterator<MainDataPropertyEntity> ite = pset.iterator();
		
		while(ite.hasNext()){
			MainDataPropertyEntity p = ite.next();
			clums = clums + p.getName() + " , ";
		}
		
		clums = delectPoint(clums);
		
		ret = ret + clums + " from " + getTableName(mdd);
		
		return ret;
	}
	
	/**
	 * 获取主数据集合
	 * @param mdd
	 * @return
	 */
	public static String getSelectSQL(MainDataMetaDataEntity mdd,int start,int max){
		String ret = "select ";
		
		List<MainDataPropertyEntity> pset = mdd.getProperties();
		
		String clums = "";
		
		Iterator<MainDataPropertyEntity> ite = pset.iterator();
		
		while(ite.hasNext()){
			MainDataPropertyEntity p = ite.next();
			clums = clums + p.getName() + " , ";
		}
		
		clums = delectPoint(clums);
		
		ret = ret + clums + " from " + getTableName(mdd) + " LIMIT " + start + "," + max;
		
		return ret;
	}
	
	/**
	 * 获取主数据副本集合
	 * @param mdd
	 * @return
	 */
	public static String getCorbonSelectSQL(MainDataMetaDataEntity mdd,String id){
		String ret = "select  ";
		
		List<MainDataPropertyEntity> pset = mdd.getProperties();
		
		String clums = buildClums(pset);
                
		ret = ret + clums + " from " + getCorbonTableName(mdd,id);
		
		return ret;
	}
	
	/**
	 * 构建主数据表的插入语句
	 * @param mdd
	 * @param properties
	 * @return
	 */
	public static String buildInsertSQL(MainDataMetaDataEntity mdd){
	
		UUID uuid = UUID.randomUUID();
		String id = uuid.toString();
		id = id.replaceAll("-", "");
		
		String sql = "insert into " + getTableName(mdd) + "( "+ buildClums(mdd.getProperties()) + ") " 
	+ "values("+ buildP(mdd.getProperties()) + ")";
		
		return sql;
	}
	
	/**
	 * 构建某主数据表的修改语句
	 * @param mdd
	 * @param properties
	 * @param maindata_id
	 * @return
	 */
	public static String buildUpdateSQL(MainDataMetaDataEntity mdd,List<MainDataPropertyEntity> properties){
		String sql = "update " + getTableName(mdd) + " set " ;
		sql = sql + buildClumsWithValue(properties) ;
		sql = sql + " where id=?";
		return sql;
	}
	
	/**
	 * 构建主数据副本表的插入语句
	 * @param mdd
	 * @param properties
	 * @return
	 */
	public static String buildCorbonInsertSQL(MainDataMetaDataEntity mdd,List<MainDataPropertyEntity> properties){
	
		UUID uuid = UUID.randomUUID();
		String id = uuid.toString();
		id = id.replaceAll("-", "");
		
		String sql = "insert into " + getCorbonTableName(mdd,"") + "( maindata_id," + buildClums(properties) + ") " 
	+ "values('" +id +"', "+ buildP(properties) + ")";
		
		return sql;
	}
	
	/**
	 * 构建某主数据副本表的修改语句
	 * @param mdd
	 * @param properties
	 * @param maindata_id
	 * @return
	 */
	public static String buildCorbonUpdateSQL(MainDataMetaDataEntity mdd,List<MainDataPropertyEntity> properties,String maindata_id){
		String sql = "update " + getCorbonTableName(mdd,"") + "set " ;
		sql = sql + buildClumsWithValue(properties) ;
		sql = sql + " where maindata_id='" + maindata_id +"'";
		return "";
	}
	
	private static String buildClumsWithValue(List<MainDataPropertyEntity> properties){
		String clums = "";
		
		for(MainDataPropertyEntity p : properties){
			clums = clums + p.getName() + "=?,";
		}
		
		clums = delectPoint(clums);
		
		return clums;
	}
	
	private static String buildClums(List<MainDataPropertyEntity> properties){
		String clums = "";
		
		for(MainDataPropertyEntity p : properties){
			clums = clums + p.getName() + ",";
		}
		
		clums = delectPoint(clums);
		
		return clums;
	}
	
	private static String buildP(List<MainDataPropertyEntity> properties){
		String clums = "";
		
		for(int i = 0 ;i < properties.size();i ++){
			clums = clums + "?,";
		}
		
		clums = delectPoint(clums);
		
		return clums;
	}
	
	/**
	 * 获取主数据表名称
	 * @param mdd
	 * @return
	 */
	public static String getTableName(MainDataMetaDataEntity mdd){
		String tablename = mdd.getEnName();
		return tablename;
	}
	
	/**
	 * 获取主数据附表表名
	 * @param mdd
	 * @return
	 */
	public static String getCorbonTableName(MainDataMetaDataEntity mdd ,String id){
		String tablename = mdd.getEnName()+id;
		
		return tablename;
	}
	
	
	/**
	 * 根据属性，生成属性SQL
	 * @param property
	 * @return
	 */
	private static String getClumSQL(MainDataPropertyEntity property){
		String clum = "";
		clum = clum + property.getName();
		clum = clum +" " +  toSQLType(property.getDatatype()) + "(" + property.getLength() + ")  null" ;
		
		return clum;
	}

	
	
	private static String toSQLType(String type){
		if(type.equalsIgnoreCase(MainDataPropertyEntity.DT_STRING)){
			return "varchar";
		}else if(type.equalsIgnoreCase(MainDataPropertyEntity.DT_INT)){
			return "int";
		}else{
			return "varchar";
		}
	}
	/**
	 * 去掉最后一个逗号
	 * @param clums
	 * @return
	 */
	private static String delectPoint(String clums){
		if(clums.lastIndexOf(",") != -1)
			clums = clums.substring(0,clums.lastIndexOf(",") );
		return clums;
	}
	
	public static void main(String[] args){
		MetaDataDao dao = new MetaDataDao();
             MainDataMetaDataEntity mdmd;
            try {
                mdmd = dao.getMainDataMetaDataByID("1");
                String sql = MainDataSQLBuilder.makeTableSQL(mdmd);
                System.out.println("sql : " + sql);
                String sql1 = MainDataSQLBuilder.makeCorbonTableSQL(mdmd,"1");
                System.out.println("sql1 : " + sql1);
            } catch (SQLException ex) {
                Logger.getLogger(MainDataSQLBuilder.class.getName()).log(Level.SEVERE, null, ex);
            }
             
	}
	
}
